import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Dimension;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.awt.event.MouseListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import javax.swing.*;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.DefaultTableModel;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;

public class PopsongPanel extends JPanel implements TLModule{

	private JTable table;
	private List<Long> songIDs;
	private List<String> songNames;
	private ArrayList<Integer> songTweetNums = new ArrayList<Integer>();
	private Framework fw;
	
	public PopsongPanel(Framework in_fw){
		fw = in_fw;
		reset();
	}	
	
	private void setTable(){
		MyModel model = new MyModel();
		model.addColumn("songID", songIDs.toArray());
		model.addColumn("songNAME", songNames.toArray());
		model.addColumn("tweetNUM", songTweetNums.toArray());
		table = new JTable(model);
		table.setTableHeader(null);
		table.setShowGrid(false);
		table.getColumnModel().getColumn(0).setMaxWidth(50);
		table.getColumnModel().getColumn(1).setMaxWidth(150);
		table.getColumnModel().getColumn(2).setMaxWidth(50);
		table.setPreferredScrollableViewportSize(new Dimension(250,100));
		table.setBackground(new Color(35,31,32));
		table.setForeground(new Color(240,240,240));
		table.setSelectionMode(ListSelectionModel.MULTIPLE_INTERVAL_SELECTION);
		DefaultTableCellRenderer dtc=new DefaultTableCellRenderer();   
        dtc.setForeground(java.awt.Color.YELLOW);   
        table.getColumnModel().getColumn(2).setCellRenderer(dtc);
		this.setBackground(new Color(203,204,206));

		// set Layout
		setLayout(new BorderLayout());
		add(new JLabel("Most Popular Songs"),BorderLayout.NORTH);
		add(new JScrollPane(table),BorderLayout.CENTER);
		
		// add listener
		MouseListener mouseListener = new MouseAdapter() {
			public void mouseClicked(MouseEvent e) {
				int[] rows = table.getSelectedRows();
				Long[] selectedID = new Long[rows.length];
				for (int i=0;i<rows.length;i++){
					selectedID[i] = songIDs.get(rows[i]);
				}
				List<Long> highLightList = Arrays.asList(selectedID);
				fw.resetPhrase();
				fw.resetSong();
				fw.highlightSong(highLightList);
		    }
		};
		
		// add listener
		table.addMouseListener(mouseListener);
	}
	
	class MyModel extends DefaultTableModel{
		public boolean isCellEditable(int x, int y){
			return false;
		}
	}
	
	public void connectDatabase(){
		Connection connection = null;
        try {
        	// Load the JDBC driver
        	String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver
        	Class.forName(driverName);

	        // Create a connection to the database
	        String serverName = "localhost";
	        String mydatabase = "infviz";
	        String url = "jdbc:mysql://" + serverName + ":3306/" + mydatabase; // a JDBC url
	        String username = "root";
	        String password = "augmented";
	        connection = DriverManager.getConnection(url, username, password);
        	} 
        catch (ClassNotFoundException e) {
	        // Could not find the database driver
	        } 
        catch (SQLException e) {
	        // Could not connect to the database
	        } 
		QueryRunner qr = new QueryRunner();
        ResultSetHandler rsh = new ColumnListHandler();
        
        String sql1 = "SELECT song_id " +
        		"FROM song_popularity " +
        		"JOIN song " +
        		"USING(song_title) " +
        		"GROUP BY song_id " +
        		"ORDER BY SUM(nr_tweets) DESC " +
        		"LIMIT 0,1000;";
        String sql2 = "SELECT song_title " +
				"FROM song_popularity " +
				"JOIN song " +
				"USING(song_title) " +
				"GROUP BY song_id " +
				"ORDER BY SUM(nr_tweets) DESC " +
				"LIMIT 0,999;";
        String sql3 = "SELECT SUM(nr_tweets) " +
				"FROM song_popularity " +
				"JOIN song " +
				"USING(song_title) " +
				"GROUP BY song_id " +
				"ORDER BY SUM(nr_tweets) DESC " +
				"LIMIT 0,999;";
		try {
			songIDs = (List<Long>) qr.query(connection, sql1, rsh);
			songNames = (List<String>) qr.query(connection, sql2, rsh);
			songTweetNums = (ArrayList<Integer>) qr.query(connection, sql3, rsh);
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}		
	}
	
	@Override
	public void highlightPhrase(List<Long> phraseId) {
		
	}

	@Override
	public void highlightSong(List<Long> songId) {
		int index;
		for (int i=0;i<songId.size();i++){
			index = this.songIDs.indexOf(songId.get(i));
			this.table.addRowSelectionInterval(index, index);
		}
		
	}

	@Override
	public void highlightUser(List<String> users) {
		
	}

	@Override
	public void reset() {
		connectDatabase();
		setTable();
	}


	@Override
	public void resetPhrase() {
	}

	@Override
	public void resetSong() {
		this.table.clearSelection();
	}

	@Override
	public void resetTime() {
		
	}

	@Override
	public void resetUser() {
		
	}

	@Override
	public void setPhrase(List<Long> phraseId) {
		
	}

	@Override
	public void setSong(List<Long> songId) {
		
	}

	@Override
	public void setTimeSpan(Timestamp start, Timestamp end) {
	
		
	}

	@Override
	public void setUser(List<String> users) {
		
	}

	@Override
	public void resetBillBoard(String chartName) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setBillBoard(String chartName) {
		// TODO Auto-generated method stub
		
	}


}
	
	